# -*- coding: UTF-8 -*-
import MySQLdb


class db():
    """docstring for db"""

    def __init__(self):
        self.field = '*'
        self.whereList = []
        self.insertData = []
        self.updateData = {}
        self.table_name = ''
    # 创建数据库连接

    def connection(self, host, port, username, password, database):
        db = MySQLdb.connect(host=host, port=int(port), user=username, passwd=password,db=database)
        self.db_connection = db
        return self

    # 设置查询数据表
    def table(self, name):
        self.table_name = name
        return self

    # 设置查询条件
    def where(self, key, eq='', value=''):
        if isinstance(key, list):
            self.whereList.appaend(key)
        else:
            if value == '':
                self.whereList.append(key + '=' + eq)
            else:
                self.whereList.append(key + eq + value)
        return self

    # 设置查询字段
    def select(self, field):
        self.field = field
        return self

    # 获取多行结果
    def get(self):
        cursor1 = self.db_connection.cursor()
        cursor1.execute(self.__stitchingSelect())
        result = cursor1.fetchall()
        return result

    def first(self):
        cursor1 = self.db_connection.cursor()
        cursor1.execute(self.__stitchingSelect())
        result = cursor1.fetchone()
        return result

    # 执行插入操作
    def insert(self, data):
        #data=data.encode("utf-8")
        if isinstance(data, dict):
            self.insertData.append(data)
            # self.db_connection.commit()
        elif isinstance(data, list) or len(data) > 0:
            if not isinstance(data[0], dict):
                return False
            else:
                self.insertData = data
        else:
            return False
        try:
            cursor1 = self.db_connection.cursor()
            cursor1.execute("SET NAMES utf8")
            res = cursor1.execute(self.__stitchingInsert())
            self.db_connection.commit()
            self.db_connection.close()
            print(res)
        #except Exception as e:
            print 'hello'
            print(e)

    def update(self, data):
        if isinstance(data, dict):
            self.updateData = data
        else:
            return False
        try:
            cursor1 = self.db_connection.cursor()
            cursor1.execute(self.__stitchingUpdate())
        except Exception as e:
            print(e)

    def delete(self):
        try:
            cursor1 = self.db_connection.cursor()
            cursor1.execute(self.__stitchingDelete())
        except Exception as e:
            print(e)

    # 拼接sql查询语句
    def __stitchingSelect(self):
        if len(self.whereList) != 1:
            ls = ''
            for key in self.whereList:
                ls = ls + key
            self.whereList = ls
        else:
            self.whereList = '1'
        sql = 'select ' + self.field + ' from ' + \
            self.table_name + ' where ' + self.whereList
        return sql

    # 拼接sql插入语句
    def __stitchingInsert(self):
        fileds = []
        values = []
        if len(self.insertData) == 0:
            return False
        else:
            fileds = self.insertData[0].keys()
            for value in self.insertData:
                ls = []
                for v2 in value:
                    ls.append('\'' + value[v2] + '\'')
                values.append('(' + ','.join(ls) + ')')
            # print(fileds)
            # print(values)
        sql = 'insert into ' + self.table_name + \
            '(' + ','.join(fileds) + ') values ' + ','.join(values) + ''
        return sql

    # 拼接sql修改语句
    def __stitchingUpdate(self):
        if len(self.whereList) > 0:
            self.whereList = ' and '.join(self.whereList)
        else:
            self.whereList = '1'
        if len(self.updateData) == 0:
            return False
        else:
            ls = []
            for v2 in self.updateData:
                ls.append(v2 + '=' + '\'' + self.updateData[v2] + '\'')
                # print(ls)
            # values.append(ls)
            # print(fileds)
            # print(values)
        sql = 'update ' + self.table_name + \
            ' set ' + ','.join(ls) + ' where ' + self.whereList
        # print(sql)
        return sql

    # 拼接sql删除语句
    def __stitchingDelete(self):
        if len(self.whereList) > 0:
            self.whereList = ' and '.join(self.whereList)
        else:
            self.whereList = '1'
        sql = 'delete from ' + self.table_name + ' where ' + self.whereList
        print(sql)
        return sql

# obj = Db()
# arr = obj.connection('localhost', 'root', '', 'cms').table(
#     'users').where('id', '1').first()
# obj.connection('localhost', 'root', '', 'test').table(
#     'test_1').where('id', '1').update({'id': '1', 'key1': 'ssd'})
#
#

# obj.connection('localhost', 'root', '', 'test').table(
#     'test_1').where('id', '1').delete()

# ls = {'Alice': '2341', 'Beth': '9102', 'Cecil': '3258'}
# print(ls.keys())
# -*- coding: UTF-8 -*-
import MySQLdb


class Db():
    """docstring for db"""

    def __init__(self):
        self.field = '*'
        self.whereList = []
        self.insertData = []
        self.updateData = {}
        self.table_name = ''
    # 创建数据库连接

    def connection(self, host, username, password, database):
        db = MySQLdb.connect(host, username, password, database)
        self.db_connection = db
        return self

    # 设置查询数据表
    def table(self, name):
        self.table_name = name
        return self

    # 设置查询条件
    def where(self, key, eq='', value=''):
        if isinstance(key, list):
            self.whereList.appaend(key)
        else:
            if value == '':
                self.whereList.append(key + '=' + eq)
            else:
                self.whereList.append(key + eq + value)
        return self

    # 设置查询字段
    def select(self, field):
        self.field = field
        return self

    # 获取多行结果
    def get(self):
        cursor1 = self.db_connection.cursor()
        cursor1.execute(self.__stitchingSelect())
        result = cursor1.fetchall()
        return result

    def first(self):
        cursor1 = self.db_connection.cursor()
        cursor1.execute(self.__stitchingSelect())
        result = cursor1.fetchone()
        return result

    # 执行插入操作
    def insert(self, data):
        if isinstance(data, dict):
            self.insertData.append(data)
            # self.db_connection.commit()
        elif isinstance(data, list):
            if not isinstance(data[0], dict):
                return False
            else:
                self.insertData = data
        else:
            return False
        try:
            cursor1 = self.db_connection.cursor()
            cursor1.execute(self.__stitchingInsert())
        except Exception as e:
            print(e)

    def update(self, data):
        if isinstance(data, dict):
            self.updateData = data
        else:
            return False
        try:
            cursor1 = self.db_connection.cursor()
            cursor1.execute(self.__stitchingUpdate())
        except Exception as e:
            print(e)

    def delete(self):
        try:
            cursor1 = self.db_connection.cursor()
            cursor1.execute(self.__stitchingDelete())
        except Exception as e:
            print(e)

    # 拼接sql查询语句
    def __stitchingSelect(self):
        if len(self.whereList) != 1:
            ls = ''
            for key in self.whereList:
                ls = ls + key
            self.whereList = ls
        else:
            self.whereList = '1'
        sql = 'select ' + self.field + ' from ' + \
            self.table_name + ' where ' + self.whereList
        return sql

    # 拼接sql插入语句
    def __stitchingInsert(self):
        fileds = []
        values = []
        if len(self.insertData) == 0:
            return False
        else:
            fileds = self.insertData[0].keys()
            for value in self.insertData:
                ls = []
                for v2 in value:
                    ls.append('\'' + value[v2] + '\'')
                values.append('(' + ','.join(ls) + ')')
            # print(fileds)
            # print(values)
        sql = 'insert into ' + self.table_name + \
            '(' + ','.join(fileds) + ') values ' + ','.join(values) + ''
        # print(sql)
        return sql

    # 拼接sql修改语句
    def __stitchingUpdate(self):
        if len(self.whereList) > 0:
            self.whereList = ' and '.join(self.whereList)
        else:
            self.whereList = '1'
        if len(self.updateData) == 0:
            return False
        else:
            ls = []
            for v2 in self.updateData:
                ls.append(v2 + '=' + '\'' + self.updateData[v2] + '\'')
                # print(ls)
            # values.append(ls)
            # print(fileds)
            # print(values)
        sql = 'update ' + self.table_name + \
            ' set ' + ','.join(ls) + ' where ' + self.whereList
        # print(sql)
        return sql

    # 拼接sql删除语句
    def __stitchingDelete(self):
        if len(self.whereList) > 0:
            self.whereList = ' and '.join(self.whereList)
        else:
            self.whereList = '1'
        sql = 'delete from ' + self.table_name + ' where ' + self.whereList
        print(sql)
        return sql

# obj = Db()
# arr = obj.connection('localhost', 'root', '', 'cms').table(
#     'users').where('id', '1').first()
# obj.connection('localhost', 'root', '', 'test').table(
#     'test_1').where('id', '1').update({'id': '1', 'key1': 'ssd'})
#
#

# obj.connection('localhost', 'root', '', 'test').table(
#     'test_1').where('id', '1').delete()

# ls = {'Alice': '2341', 'Beth': '9102', 'Cecil': '3258'}
# print(ls.keys())
